from django.shortcuts import render
from django import forms
from openpyxl import load_workbook
from mes import models
from django.shortcuts import HttpResponse, render, redirect
from django.db import transaction
import xlrd
import pymysql
import datetime
# Create your views here.


def upload(request):
    return render(request,"upload.html")


def excel_upload(request):
    if request.method == "POST":
        file = request.FILES['my_file']
        print(file)
        type_file = file.name.split('.')[1]
        print(type_file)
        if type_file == 'xlsx':
            #读取上传的excel文档
            read_file = xlrd.open_workbook(filename=None,file_contents=file.read())
            #获取第一个工作表
            file_table = read_file.sheets()[0]
            print(file_table)
            file_table_rows = file_table.nrows
            print(file_table_rows)
            date = datetime.datetime.now()
            try:
                #事务处理，这个好像是如果没有成功导入，会回滚，是学习点
                with transaction.atomic():
            #         # i = 1
            #         # while i < file_table_rows:
                    for i in range(1,file_table_rows):
                        print(i)
                        aa=(file_table.cell(i,0)).value
                        print("aa:"+aa)
                        bb = (file_table.cell(i, 1)).value
                        print("bb:"+str(bb))
                        cc = (file_table.cell(i, 2)).value
                        print("cc:"+cc)
                        dd = (file_table.cell(i, 3)).value
                        print("dd:"+dd)
                        ee = (file_table.cell(i, 4)).value
                        print("ee:"+ee)
                        # # file_table_rows_values = file_table_rows.values(i)
                        # models.out_record.objects.create(item_no="aa",numbers="dd",item_name = "cc", customer = "fff", out_address = "ee")
                        connect = pymysql.connect(host='127.0.0.1',user='changer',password='wajy52111',port=3306,db='mes')
                        corsor = connect.cursor()
                        sql = "insert into out_record(item_no,item_name,numbers,out_address,out_time) value('%s','%s','%s','%s','%s')" % (aa,cc,dd,ee,date)
                        corsor.execute(sql)
                        connect.commit()
                        corsor.close()
                        connect.close()

            except Exception as e:
                return render(request,"upload1.html",{'msg':e})
            return render(request,"upload1.html",{'msg':"上传成功"})
            return HttpResponse(render(request,"upload1.html",{'msg':"上传的不是XSLX"}))
        return HttpResponse(render(request,"upload1.html",{'msg':"不是POST请求"}))
